﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_AccountAdd]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_AccountAdd];
GO
CREATE PROCEDURE [dbo].[sproc_AccountAdd]
(
    @UserName         nvarchar(20),
    @Password           nvarchar(20),
    @SenderName     nvarchar(20),
    @CnName              nvarchar(20),
    @EnName              nvarchar(20),
    @EmailAddress        nvarchar(20),
    @Descn               nvarchar(255),
    @CurrentUserID  [int] OUTPUT
 )
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

    DECLARE @CurrentError int;

    -- start transaction, inserting into two tables
    BEGIN TRANSACTION;

    -- insert record to Index Table
    INSERT INTO dbo.TabAccountIndex (username, [password]) VALUES (@UserName, @Password);
    select @CurrentError = @@error, @CurrentUserID = @@identity;
    IF @CurrentError != 0
        goto ERROR_HANDLER;

    --begin to insert a new record to TabAccountInfo
    INSERT INTO dbo.TabAccountInfo (userID,SenderName,CnName,EnName, EmailAddress,Descn)
    VALUES (@CurrentUserID, @SenderName, @CnName, @EnName, @EmailAddress,@Descn);
    select @CurrentError = @@Error;
    IF @CurrentError != 0
        goto ERROR_HANDLER;
    --End to insert into TabAccountInfo
    
    --begin to set default mail folders to User
    INSERT INTO dbo.TabUsrMailFolder (FolderName,UserID,Attribute)
    SELECT FolderName, @CurrentUserID as UserID, 0 As Attribute
    FROM dbo.TabSysMailFolder;
    select @CurrentError = @@Error;
    IF @CurrentError != 0
        goto ERROR_HANDLER;
    --set default mail folders end
  
    -- end of transaction
    COMMIT TRANSACTION;
    
    -- return 0 to indicate success, otherwise the raised error will be returned
    RETURN 0;

ERROR_HANDLER:
    ROLLBACK TRANSACTION;
    RETURN @CurrentError;

END
GO